import shutil
import os
from sortingdataprocess import *


# pd.set_option('display.float_format', lambda x: '%.3f' % x)


def first_coef(form2_infos, machine_name, cnxn, conn, engine, logger, machine_label):
    flag = 0
    flag2 = False
    lotID = None
    dict_coef_data = {}
    # 数据库存储校准NG记录:NG(状态)、NG步骤、NG原因，其他同OK记录，除时间外全为空
    NG_dict = {'MoID': form2_infos['MoID'], 'Machine_Label': machine_label,
               'LotID': lotID,
               'Status': 0}
    # 创建游标
    cursor = cnxn.cursor()
    cursor.fast_executemany = True
    # 创建游标
    cursor1 = conn.cursor()
    cursor1.fast_executemany = True
    # 查询品号
    item_code = get_item_code(form2_infos['MoID'])
    # 标准件数据
    std_data = get_standpart_data(item_code)
    # 读取测试数据
    test_data, error = get_test_data(form2_infos['temp_path'], machine_name, logger)

    # 查询工单+lotID组合下的LOTID2是否存在，存在+1，不存在初始化为1
    cursor.execute(
        f"SELECT MAX(LotID2) FROM Gencoef_Info1 WHERE MOID='{form2_infos['MoID']}'")
    rows = cursor.fetchall()
    if rows[0][0] is None:
        lotID2 = 1
    else:
        lotID2 = rows[0][0] + 1

    # 添加LOTID2到列表
    form2_infos['LotID2'] = lotID2
    NG_dict['LotID2'] = lotID2

    # 创建上传与下载目录并添加到列表
    uploadsDir = os.path.join('uploads', 'rawdata', str(datetime.now().year), str(datetime.now().month))
    if not os.path.exists(uploadsDir):
        os.makedirs(uploadsDir)

    # 修改上传文件名并保存
    ext = os.path.splitext(form2_infos['temp_path'])[1]
    upnew_filename = 'raw-' + form2_infos['MoID'] + '-' + str(lotID2) + ext
    upload_path = os.path.join(uploadsDir, upnew_filename)
    form2_infos['UploadPath'] = upload_path
    shutil.copy(form2_infos['temp_path'], form2_infos['UploadPath'])

    # 判断测试数据和上传数据的有效性

    if test_data.empty and std_data.empty:
        logger.error("未找到有效标准件数据,且上传数据读取发生错误！")
        flash_result = f"未查询到标准件有效数据<br>上传文件读取错误,错误描述:{error}"
        NG_dict['NG_Site'] = "校准第一步:验证标准件数据以及测试数据"
        NG_dict['Reason'] = f"标准件数据无效且测试数据读取错误！"
        return flash_result, dict_coef_data, lotID2, flag, NG_dict

    elif test_data.empty and std_data.empty:
        logger.error("未找到有效标准件数据,且上传数据有误！")
        flash_result = f"未查询到标准件有效数据"
        NG_dict['NG_Site'] = "校准第一步:验证标准件数据以及测试数据"
        NG_dict['Reason'] = f"标准件数据无效！"
        return flash_result, dict_coef_data, lotID2, flag, NG_dict

    elif test_data.empty and not std_data.empty:
        logger.error("上传数据读取发生错误！")
        flash_result = f"上传文件读取错误,错误描述:{error}"
        NG_dict['NG_Site'] = "校准第一步:验证标准件数据以及测试数据"
        NG_dict['Reason'] = f"测试数据读取错误！"
        return flash_result, dict_coef_data, lotID2, flag, NG_dict

    else:
        coef_data = cal_coef(std_data, test_data)
        # 编码对应判断
        list1 = sorted(list(std_data['StandardPartNO']))
        for i in list(test_data['TestNO']):
            if i not in list1:
                flag2 = True
        if flag2:
            logger.error("测试数据中编码与标准件不对应！")
            flash_result = "测试数据与标准数据编码不对应"
            NG_dict['NG_Site'] = "校准第一步:验证测试数据与标准件数据编号"
            NG_dict['Reason'] = f"测试数据与标准件数据编号不对应！"
            return flash_result, dict_coef_data, lotID2, flag, NG_dict

        else:
            flash_result = coef_data
            logger.info(f'{form2_infos["MachineID"]}号中间系数计算成功')
            pars = ['MoID', 'StandardPartID', 'MachineID', 'LotID2']
            for i in pars:
                test_data[i] = form2_infos[i]
            try:
                # 数据写入数据库
                # i_sql = get_insert_sql('Gencoef_Data1', test_data)
                # engine.execute(i_sql)
                test_data.to_sql('Gencoef_Data1', engine, if_exists='append', index=False)
            except Exception as e:
                logger.error(f"{form2_infos['MoID']}工单--校准数据存储错误,{e}")
                flash_result = f"校准数据存储错误！,问题描述：{e}"
                NG_dict['NG_Site'] = "校准第一步:校准数据-Gencoef_Data1存储"
                NG_dict['Reason'] = f"Gencoef_Data1存储出错！"
                return flash_result, dict_coef_data, lotID2, flag, NG_dict
            else:
                dict_coef_data = flash_result.loc['校准系数'].to_dict()
                # 查询COF中是否存在之前订单、机台一样的数据
                sql_select = f"SELECT max(StandardPartNO) FROM COF WHERE MoID='{form2_infos['MoID']}' and CF='校准系数' and " \
                             f"MachineID='{form2_infos['MachineID']}'"
                row = cursor1.execute(sql_select).fetchall()
                if row[0][0]:
                    No = int(row[0][0]) + 1
                else:
                    No = 1
                sql_insert = f"INSERT INTO COF (MoID,CF,ITEM_CODE,StandardPartNO," \
                             f"dU,dx,dy,dRa,dR9,dlm,dI,MachineID,Updatetime,LotID) " \
                             f"values ('{form2_infos['MoID']}','校准系数','{item_code}','{No}'," \
                             f"{dict_coef_data['dU']},{dict_coef_data['dx']},{dict_coef_data['dy']}," \
                             f"{dict_coef_data['dRa']},{dict_coef_data['dR9']},{dict_coef_data['dlm']}," \
                             f"{dict_coef_data['dI']},'{form2_infos['MachineID']}','{datetime.now()}'," \
                             f"'{str(lotID2)}')"
                try:
                    cursor1.execute(sql_insert)
                    conn.commit()
                except Exception as e:
                    conn.rollback()
                    logger.error(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--校准系数存储错误,{e}")
                    flash_result = "校准系数存储错误！"
                    NG_dict['NG_Site'] = "校准第二步:校准系数-COF表存储"
                    NG_dict['Reason'] = f"COF表存储失败！"
                else:
                    # 列表插入当前时间
                    form2_infos['UpdateTime'] = str(datetime.now())
                    # 删除字典temp_path及临时文件
                    os.remove(form2_infos.pop('temp_path'))
                    # 遍历字典
                    kvs = [(k, v) for k, v in form2_infos.items() if v is not None]
                    # 创建sql语句
                    sql_data = f"INSERT INTO Gencoef_Info1({','.join(i[0] for i in kvs)}) " \
                               f"VALUES ({','.join(repr(i[1]) for i in kvs)});"
                    try:
                        cursor.execute(sql_data)
                        cnxn.commit()
                    except Exception as e:
                        cnxn.rollback()
                        logger.error(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--校准信息存储错误,{e}")
                        flash_result = f"校准信息-存储错误！问题描述：{e}"
                        NG_dict['NG_Site'] = "校准第二步:校准信息-Gencoef_Info1存储"
                        NG_dict['Reason'] = f"Gencoef_Info1存储出错！"
                    else:
                        flag = 1
                        flash_result = flash_result.to_json(orient='records', force_ascii=False)
                        logger.info(
                            f"{form2_infos['MachineID']}号，{form2_infos['MoID']}工单--中间系数计算成功")
    return flash_result, dict_coef_data, lotID2, flag, NG_dict
